
package db;



import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import log.Logger;

import javax.ws.rs.core.Cookie;
import java.sql.*;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * User: SLeeP
 * Date: 16.04.12
 * Time: 3:42
 */
public class DBConnection {

    public enum Operation {
        LOGIN_OPERATION,
        LOGOUT,
        ADD_CLIENT,
        TRANSACTION,
        ADD_ACCOUNT,
        CLOSE_ACCOUNT,
        PUT_MONEY,
        GET_USER_INFO,
        GET_ACCOUNTS,
        GET_ACCOUNT_HISTORY,
        SEND_MESSAGE,
        REPLY,
        ADD_COOKIE,
        USER_BLOCKED,
        USER_UNBLOCKED,
        GET_MESSAGE_BY_ID,
        GET_MESSAGES,
        GET_NUMBER_OF_UNREAD_MESSAGES,
        GET_NUMBER_OF_MESSAGES,
        GET_USER_NAME,
        MARK_MESSAGE_READ,
        MARK_MESSAGE_UNREAD
    }

    public enum Role {
        ADMIN,
        MODERATOR,
        CLIENT
    }

    public enum OwnerType {
        CLIENT,
        ORGANIZATION
    }

    public enum Mobile_Operator {
        MTS,
        MEGAFON,
        BEELINE
    }

    private Connection connection = null;
    private final static String userName = "webbanking";
    private final static String password = "bank";
    private final static String url = "jdbc:mysql://localhost:3307/webbanking";
    private final static String className = "com.mysql.jdbc.Driver";
    private final static int account_id_lendth = 16;
    public String lastOperationError = "";

    public DBConnection() {
        try {
            Class.forName(className).newInstance ();
            connection = DriverManager.getConnection (url, userName, password);
            connection.setAutoCommit(false);
            Logger.write("Database connection established", Logger.Mode.ACTION);
        } catch (Exception e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    
    private int insert(String tableName, String[] columns, String[] values) {
        PreparedStatement preparedStatement = null;
        try {
            String cols = "";
            String vals = "";
            if (columns.length >= 1) {
                cols = "(" + columns[0];
                vals = "(" + "?";
            }
            for (int i = 1; i != columns.length; i++) {
                cols += ", " + columns[i];
                vals += ", ?";
            }
            cols += ")";
            vals += ")";
            String query = "INSERT INTO " + tableName + " " + cols + " values " + vals;
            preparedStatement = connection.prepareStatement(query);

            for (int i = 0; i != values.length; i++) {
                preparedStatement.setString(i + 1, values[i]);
                query = query.replaceFirst("[?]", values[i]);
            }

            Logger.write(query , Logger.Mode.ACTION);
            try {
                int mod = preparedStatement.executeUpdate();
                if (mod != 0) {
                    Statement statement = connection.createStatement();
                    ResultSet resultSet = statement.executeQuery("select LAST_INSERT_ID() as id");
                    if (resultSet.next()) {
                        return resultSet.getInt("id");
                    } else {
                        return -1;
                    }
                } else {
                    return -1;
                }
            } catch (MySQLIntegrityConstraintViolationException e) {
                lastOperationError = e.getMessage();
                Logger.write(e.getMessage() , Logger.Mode.ERROR);
                return -1;
            } finally {
                preparedStatement.close();
            }

        } catch (SQLException e) {
            lastOperationError = e.getMessage();
            Logger.write(e.getMessage() , Logger.Mode.ERROR);
        }

        return 0;
    }

    private ResultSet select(String tableName, String[] columns, String[] values) {
        PreparedStatement preparedStatement = null;
        try {
            String where = "";
            String query;
            if (columns.length >= 1) {
                where = columns[0] + " = ?";
                for (int i = 1; i != columns.length; i++) {
                    where += " and " + columns[i] + " = ?";
                }
                query = "SELECT * FROM " + tableName + " WHERE " + where;
            } else {
                query = "SELECT * FROM " + tableName;
            }

            preparedStatement = connection.prepareStatement(query);

            for (int i = 0; i != values.length; i++) {
                preparedStatement.setString(i + 1, values[i]);
                query = query.replaceFirst("[?]", "'" + values[i] + "'");
            }
            Logger.write(query , Logger.Mode.ACTION);
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            lastOperationError = e.getMessage();
            if (preparedStatement != null) {
                Logger.write(e.getMessage() , Logger.Mode.ERROR);
            }
            return null;
        }
    }
    
    private int getLastOperationId() {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("select max(operation_id) as operation_id from operation");
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                int res = resultSet.getInt("operation_id");
                preparedStatement.close();
                return res;
            } else {
                preparedStatement.close();
                return -1;
            }
        } catch (SQLException e) {
            lastOperationError = e.getMessage();
            Logger.write(e.getMessage() , Logger.Mode.ERROR);
            return -1;
        }
    }

    public Role getRole(int id) {
        try {
            Savepoint savepoint = connection.setSavepoint();
            String[] columns = {"user_id"};
            String[] values = {id + ""};
            ResultSet resultSet = select("user", columns, values);
            if (resultSet == null) {
                connection.rollback(savepoint);
                return null;
            }

            Role role;
            if (resultSet.next()) {
                return Role.valueOf(resultSet.getString("role"));
            }

            return null;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return null;
        } finally {
        }
    }

    public boolean addUser(int my_id,String login, String password, Role role, String firstName, String lastName, String otherName, int sex,
                           String birthDate, String birthPlace, String passportNumber, String passportPlace,
                           String passportDate, String country, String city, String address, String postIndex, String note) {

        Logger.write("ADD USER STARTED", Logger.Mode.ACTION);
        Role my_role = getRole(my_id);
        try {
            Savepoint savepoint = connection.setSavepoint();
            if (my_id == -1) {                
                return false;
            }

            if (my_role == Role.MODERATOR || my_role == Role.ADMIN) {
                String[] columns = {"firstname", "lastname","othername","sex", "role", "birthDate", "birthplace", "passportnumber", "passportplace", "passportdate","country", "city", "address", "postindex"};
                String[] values = {firstName, lastName, otherName, sex + "", role.toString(), birthDate, birthPlace, passportNumber, passportPlace, passportDate, country, city, address, postIndex};
                int res = insert("user", columns, values);
                if (res == -1) {                    
                    connection.rollback(savepoint);
                    return false;
                }
                ResultSet user = select("user", columns, values);
                if (user == null) {
                    connection.rollback(savepoint);
                    return false;
                }

                int user_id = -1;
                if (user.next()) {
                    user_id = user.getInt("user_id");
                }

                if (user_id == -1) {
                    connection.rollback(savepoint);
                    return false;
                }

                String[] columns2 = {"user_id", "login", "password"};
                String[] values2 = {user_id + "", login, password};

                int count = insert("login", columns2, values2);
                if (count == -1) {
                    connection.rollback(savepoint);
                    return false;
                }

                String[] columns3 = {"name", "note"};
                String[] values3 = {Operation.ADD_CLIENT.toString(), note};

                count = insert("operation", columns3, values3);
                if (count == -1) {
                    connection.rollback(savepoint);
                    return false;
                }
                int lastOpID = getLastOperationId();

                if (lastOpID != -1) {
                    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    Calendar cal = Calendar.getInstance();
                    String time = dateFormat.format(cal.getTime()).replace("/", "-");
                    String[] columns4 = {"user_id", "operation_id", "date"};
                    String[] values4 = {user_id + "", lastOpID + "", time};
                    count = insert("log", columns4, values4);
                } else {
                    connection.rollback(savepoint);
                    return false;
                }

                if (count == -1 || count == 0) {
                    connection.rollback(savepoint);
                    return false;
                }
                connection.commit();
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            lastOperationError = e.getMessage();
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("ADD USER FINISHED", Logger.Mode.ACTION);
        }
    }

    public int login(String login, String password) {
        Logger.write("LOGIN STARTED", Logger.Mode.ACTION);
        int user_id = -1;
        try {
            Savepoint savepoint = connection.setSavepoint();
            String[] columns = {"login", "password", "isblocked"};
            String[] values = {login, password, 0 + ""};
            ResultSet resultSet = select("login", columns, values);
            if (resultSet.next()) {
                user_id = resultSet.getInt("user_id");

                String[] columns3 = {"name"};
                String[] values3 = {Operation.LOGIN_OPERATION.toString()};

                int count = insert("operation", columns3, values3);
                if (count == -1) {
                    connection.rollback(savepoint);
                    return -1;
                }
                int lastOpID = getLastOperationId();

                if (lastOpID != -1) {
                    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    Calendar cal = Calendar.getInstance();
                    String time = dateFormat.format(cal.getTime()).replace("/", "-");
                    String[] columns4 = {"user_id", "operation_id", "date"};
                    String[] values4 = {user_id + "", lastOpID + "", time};
                    count = insert("log", columns4, values4);
                } else {
                    connection.rollback(savepoint);
                    return -1;
                }

                if (count == -1 || count == 0) {
                    connection.rollback(savepoint);
                    return -1;
                }
                connection.commit();
                return user_id;
            } else {
                return -1;
            }
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return -1;
        } catch (Exception e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return -1;
        } finally {
            Logger.write("LOGIN FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean logout(int my_id, String secret) {
        Logger.write("LOGOUT STARTED", Logger.Mode.ACTION);
        try {
            Savepoint savepoint = connection.setSavepoint();
            String[] columns3 = {"name"};
            String[] values3 = {Operation.LOGOUT.toString()};

            int count = insert("operation", columns3, values3);
            if (count == -1) {
                connection.rollback(savepoint);
                return false;
            }
            int lastOpID = getLastOperationId();

            if (lastOpID != -1) {
                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                Calendar cal = Calendar.getInstance();
                String time = dateFormat.format(cal.getTime()).replace("/", "-");
                String[] columns4 = {"user_id", "operation_id", "date"};
                String[] values4 = {my_id + "", lastOpID + "", time};
                count = insert("log", columns4, values4);
            } else {
                connection.rollback(savepoint);
                return false;
            }

            if (count == -1 || count == 0) {
                connection.rollback(savepoint);
                return false;
            }
            disableCookie(my_id, secret);
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("LOGOUT FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean addAccount(int my_id, int owner_id, OwnerType ownerType, String note) {
        return addAccount(my_id, owner_id, ownerType, 0, note);
    }

    public boolean addAccount(int my_id, int owner_id, OwnerType ownerType, double startMoney, String note) {
        Logger.write("ADD ACCOUNT STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);
        String account_id = generateAccountID();
        System.out.println(account_id);
        try {
            if (role == Role.MODERATOR) {
                Savepoint savepoint = connection.setSavepoint();
                String col1 = "";
                if (ownerType.equals(OwnerType.CLIENT)) {
                    col1 = "user_id";
                } else if (ownerType.equals(OwnerType.ORGANIZATION)) {
                    col1 = "org_id";
                }

                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                Calendar cal = Calendar.getInstance();
                String time = dateFormat.format(cal.getTime()).replace("/", "-");


                String[] columns = {"account_id", col1, "money", "dateopened"};
                String[] values = {account_id, owner_id + "", startMoney + "", time};

                int count = insert("account", columns, values);

                if (count == -1) {
                    connection.rollback(savepoint);
                    return false;
                }

                String[] columns3 = {"name", "account2_id", "amount", "note"};
                String[] values3 = {Operation.ADD_ACCOUNT.toString(), account_id + "", startMoney + "", note};

                count = insert("operation", columns3, values3);
                if (count == -1) {
                    connection.rollback(savepoint);
                    return false;
                }
                int lastOpID = getLastOperationId();

                if (lastOpID != -1) {
                    String[] columns4 = {col1, "operation_id", "date"};
                    String[] values4 = {owner_id + "", lastOpID + "", time};
                    count = insert("log", columns4, values4);
                } else {
                    connection.rollback(savepoint);
                    return false;
                }

                if (count == -1 || count == 0) {
                    connection.rollback(savepoint);
                    return false;
                }
                connection.commit();
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("ADD ACCOUNT FINISHED", Logger.Mode.ACTION);
        }
    }       

    public boolean closeAccount(int my_id, String account_id, String note) {
        Logger.write("CLOSE ACCOUNT STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);
        if (role != Role.MODERATOR) {
            return false;
        }
        try {

            Savepoint savepoint = connection.setSavepoint();

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "update account set dateclosed = \"" + time + "\" where account_id = \"" + account_id + "\""
            );
            int count = preparedStatement.executeUpdate();

            if (count == 0) {
                connection.rollback(savepoint);
                return false;
            }

            String[] columns3 = {"name", "account1_id", "note"};
            String[] values3 = {Operation.CLOSE_ACCOUNT.toString(), account_id + "", note};
            count = insert("operation", columns3, values3);
            if (count == -1) {
                connection.rollback(savepoint);
                return false;
            }
            int lastOpID = getLastOperationId();

            if (lastOpID != -1) {
                String[] columns4 = {"user_id", "operation_id", "date"};
                String[] values4 = {my_id + "", lastOpID + "", time};
                count = insert("log", columns4, values4);
            } else {
                connection.rollback(savepoint);
                return false;
            }

            if (count == -1 || count == 0) {
                connection.rollback(savepoint);
                return false;
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("CLOSE ACCOUNT FINISHED", Logger.Mode.ACTION);
        }
    }
    
    public boolean doTransaction(int my_id, String account_from, String account_to, double amount, String note) {
        Logger.write("TRANSACTION STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);
        if (role == Role.ADMIN) {
            return false;
        }
        if (amount < 0 || amount < 0.01) {
            return false;
        }

        if (account_from.equals(account_to)) {
            return false;
        }
        try {

            Savepoint savepoint = connection.setSavepoint();
            String[] columns = {"account_id"};
            String[] values = {account_from + ""};

            double amount_from = 0;
            double amount_to = 0;
            String from_id = "";
            ResultSet resultSet = select("account", columns, values);
            if (resultSet.next()) {
                amount_from = resultSet.getDouble("money");
                from_id = resultSet.getString("user_id");
                if (from_id == null) {
                    from_id = resultSet.getString("org_id");
                }
            } else {
                connection.rollback(savepoint);
                return false;
            }

            if (!from_id.equals(my_id + "") && role != Role.MODERATOR) {
                return false;
            }

            if (amount_from < amount) {
                connection.rollback(savepoint);
                return false;
            }

            String[] columns2 = {"account_id"};
            String[] values2 = {account_to + ""};

            ResultSet resultSet2 = select("account", columns2, values2);
            if (resultSet2.next()) {
                amount_to = resultSet2.getDouble("money");
            } else {
                connection.rollback(savepoint);
                return false;
            }

            PreparedStatement preparedStatement = connection.prepareStatement("update account set money = " +
                    (amount_from - amount) + "where account_id = " + account_from);
            PreparedStatement preparedStatement2 = connection.prepareStatement("update account set money = " +
                    (amount_to + amount) + "where account_id = " + account_to);
            int count = preparedStatement.executeUpdate();
            if (count != 1) {
                connection.rollback(savepoint);
                return false;
            }

            count = preparedStatement2.executeUpdate();
            if (count != 1) {
                connection.rollback(savepoint);
                return false;
            }

            String[] columns3 = {"name", "account1_id", "account2_id", "amount", "note"};
            String[] values3 = {Operation.TRANSACTION.toString(), account_from + "", account_to + "", amount + "", note};
            count = insert("operation", columns3, values3);
            if (count == -1) {
                connection.rollback(savepoint);
                return false;
            }
            int lastOpID = getLastOperationId();

            if (lastOpID != -1) {
                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                Calendar cal = Calendar.getInstance();
                String time = dateFormat.format(cal.getTime()).replace("/", "-");
                String[] columns4 = {"user_id", "operation_id", "date"};
                String[] values4 = {my_id + "", lastOpID + "", time};
                count = insert("log", columns4, values4);
            } else {
                connection.rollback(savepoint);
                return false;
            }

            if (count == -1 || count == 0) {
                connection.rollback(savepoint);
                return false;
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("TRANSACTION FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean doPutMoney(int my_id, String account_id, double amount, String note) {
        Logger.write("PUT MONEY STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);
        try {
            if (role != Role.MODERATOR) {
                return false;
            }

            Savepoint savepoint = connection.setSavepoint();
            String[] columns = {"account_id"};
            String[] values = {account_id + ""};

            double amount_has = 0;

            ResultSet resultSet = select("account", columns, values);
            if (resultSet.next()) {
                amount_has = resultSet.getDouble("money");
            } else {
                connection.rollback(savepoint);
                return false;
            }

            PreparedStatement preparedStatement = connection.prepareStatement("update account set money = " +
                    (amount_has + amount) + "where account_id = " + account_id);
            int count = preparedStatement.executeUpdate();
            if (count != 1) {
                connection.rollback(savepoint);
                return false;
            }

            String[] columns3 = {"name", "account2_id", "amount", "note"};
            String[] values3 = {Operation.PUT_MONEY.toString(), account_id + "", amount + "", note};
            count = insert("operation", columns3, values3);
            if (count == -1) {
                connection.rollback(savepoint);
                return false;
            }
            int lastOpID = getLastOperationId();

            if (lastOpID != -1) {
                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                Calendar cal = Calendar.getInstance();
                String time = dateFormat.format(cal.getTime()).replace("/", "-");
                String[] columns4 = {"user_id", "operation_id", "date"};
                String[] values4 = {my_id + "", lastOpID + "", time};
                count = insert("log", columns4, values4);
            } else {
                connection.rollback(savepoint);
                return false;
            }

            if (count == -1 || count == 0) {
                connection.rollback(savepoint);
                return false;
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("PUT MONEY FINISHED", Logger.Mode.ACTION);
        }
    }
    
    public boolean hasAccsess(int my_id, int user_id) {
        Role role = getRole(my_id);
        Role role2 = getRole(user_id);
        if (my_id != user_id && role == Role.CLIENT) {
            return false;
        }

        if (role == Role.ADMIN && role2==Role.CLIENT) {
            return false;
        }
        if (role == Role.MODERATOR && role2==Role.ADMIN) {
            return false;
        }
        if (role == Role.MODERATOR && role2!=Role.CLIENT) {
            return false;
        }

        return true;
    }

    public User getUserInfo(int my_id, int user_id) {
        Logger.write("GET USER INFO STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);
        Role role2 = getRole(user_id);

        if (my_id != user_id) {
            if (role.equals(Role.MODERATOR)) {
                if (!role2.equals(Role.CLIENT)) {
                    return null;
                }
            } if (role.equals(Role.ADMIN)) {
                if (!role2.equals(Role.MODERATOR) && role2.equals(Role.ADMIN)) {
                    return null;
                }
            } else {
                return null;
            }
        }

        if (role == Role.ADMIN && getRole(user_id)==Role.CLIENT) {
            return null;
        }

        try {
            Savepoint savepoint = connection.setSavepoint();
            String[] columns = {"user_id"};
            String[] values = {user_id + ""};
            String firstName;
            String lastName;
            String otherName;
            String sex;
            String birthDate;
            String birthPlace;
            String passportNumber;
            String passportPlace;
            String passportDate;
            String country;
            String city;
            String address;
            String postIndex;
            
            ResultSet resultSet = select("user", columns, values);
            if (resultSet.next()) {
                firstName = resultSet.getString("firstName");
                lastName = resultSet.getString("lastName");
                otherName = resultSet.getString("othername");
                if (otherName == null) {
                    otherName = "";
                }
                if (resultSet.getInt("sex") == 1) {
                    sex = "male";
                } else {
                    sex = "female";
                }
                birthDate = resultSet.getString("birthDate");
                birthPlace = resultSet.getString("birthPlace");
                if (birthPlace == null) {
                    birthPlace = "";
                }
                passportNumber = resultSet.getString("passportNumber");
                passportPlace = resultSet.getString("passportNumber");
                passportDate = resultSet.getString("passportDate");
                country = resultSet.getString("country");
                city = resultSet.getString("city");
                address = resultSet.getString("address");
                postIndex = resultSet.getString("postIndex");
                if (postIndex == null) {
                    postIndex = "";
                }

            } else {
                connection.rollback(savepoint);
                return null;
            }

            String[] columns3 = {"name"};
            String[] values3 = {Operation.GET_USER_INFO.toString()};
            int count = insert("operation", columns3, values3);
            if (count == -1) {
                connection.rollback(savepoint);
                return null;
            }
            int lastOpID = getLastOperationId();

            if (lastOpID != -1) {
                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                Calendar cal = Calendar.getInstance();
                String time = dateFormat.format(cal.getTime()).replace("/", "-");
                String[] columns4 = {"user_id", "operation_id", "date"};
                String[] values4 = {user_id + "", lastOpID + "", time};
                count = insert("log", columns4, values4);
            } else {
                connection.rollback(savepoint);
                return null;
            }

            if (count == -1 || count == 0) {
                connection.rollback(savepoint);
                return null;
            }
            connection.commit();
            return new User(firstName, lastName, otherName, sex, birthDate, birthPlace, passportNumber, passportPlace, passportDate, country, city, address, postIndex);
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return null;
        } finally {
            Logger.write("GET USER INFO FINISHED", Logger.Mode.ACTION);
        }
    }

    public ArrayList<Account> getAccounts(int my_id, int owner_id, OwnerType ownerType) {
        Logger.write("GET ACCOUNTS STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);

        if (my_id != owner_id && role != Role.MODERATOR) {
            return null;
        }
        try {
            Savepoint savepoint = connection.setSavepoint();
            String col1 = "";
            ArrayList<Account> resultList = new ArrayList<Account>();

            if (ownerType.equals(OwnerType.CLIENT)) {
                col1 = "user_id";
            } else if (ownerType.equals(OwnerType.ORGANIZATION)) {
                col1 = "org_id";
            }

            String[] columns = {col1};
            String[] values = {owner_id + ""};

            String accountNumber;
            Double money;
            String opened;
            String closed;


            ResultSet resultSet = select("account", columns, values);
            while (resultSet.next()) {
                accountNumber = resultSet.getString("account_id");
                money = resultSet.getDouble("money");
                opened = resultSet.getString("dateOpened");
                closed = resultSet.getString("dateClosed");
                if (closed == null) {
                    closed = "&mdash;";
                }
                Account account = new Account(accountNumber, money, opened, closed);
                resultList.add(account);
            }

            String[] columns3 = {"name"};
            String[] values3 = {Operation.GET_ACCOUNTS.toString()};
            int count = insert("operation", columns3, values3);
            if (count == -1) {
                connection.rollback(savepoint);
                return null;
            }
            int lastOpID = getLastOperationId();

            if (lastOpID != -1) {
                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                Calendar cal = Calendar.getInstance();
                String time = dateFormat.format(cal.getTime()).replace("/", "-");
                String[] columns4 = {"user_id", "operation_id", "date"};
                String[] values4 = {my_id + "", lastOpID + "", time};
                count = insert("log", columns4, values4);
            } else {
                connection.rollback(savepoint);
                return null;
            }

            if (count == -1 || count == 0) {
                connection.rollback(savepoint);
                return null;
            }
            connection.commit();
            return resultList;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return null;
        } finally {
            Logger.write("GET ACCOUNTS FINISHED", Logger.Mode.ACTION);
        }
    }

    public ArrayList<HistoryUnit> getAccountHistory(int my_id, String account_id) {
        Calendar cal = Calendar.getInstance();
        String oldTime = "1000-01-01";
        String newTime = "3000-01-01";
        return getAccountHistory(my_id, account_id, oldTime, newTime);
    }
    
    public ArrayList<HistoryUnit> getAccountHistory(int my_id, String account_id, String dateFrom) {
        Calendar cal = Calendar.getInstance();
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
        String time = dateFormat.format(cal.getTime()).replace("/", "-");
        return getAccountHistory(my_id, account_id, dateFrom, time);
    }


    public ArrayList<HistoryUnit> getAccountHistory(int my_id, String account_id, String dateFrom, String dateTo) {
        Logger.write("GET ACCOUNT HISTORY STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);

        if (role == Role.ADMIN) {
            return null;
        }

        try {
            Savepoint savepoint = connection.setSavepoint();
            ArrayList<HistoryUnit> resultList = new ArrayList<HistoryUnit>();
            
            String[] columns = {"account_id"};
            String[] values = {account_id + ""};
          
            ResultSet resultSet = select("account", columns, values);
            String owner_id = "";
            while (resultSet.next()) {
                owner_id = resultSet.getString("user_id");
                if (owner_id == null) {
                    owner_id = resultSet.getString("org_id");
                }

            }

            if (!owner_id.equals(my_id + "") && role != Role.MODERATOR) {
                connection.rollback(savepoint);
                return null;
            }

            String date;
            String operationName;
            double money;
            String note;

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "select * from " +
                    "operation join log on log.operation_id = operation.operation_id " +
                    "where (account1_id = " + account_id + " or account2_id = " + account_id + ") " +
                    "and date between \"" + dateFrom + "\" and \"" + dateTo +"\"");

            ResultSet resultSet1 = preparedStatement.executeQuery();

            while (resultSet1.next()) {
                date = resultSet1.getString("date");
                operationName = resultSet1.getString("name");
                money = resultSet1.getDouble("amount");

                String a = resultSet1.getString("account1_id");
                
                if (a != null && a.equals(account_id) && money != 0 ) {
                    money *= -1;
                }
                note = resultSet1.getString("note");
                resultList.add(new HistoryUnit(date, operationName, money, note));
            }

            String[] columns3 = {"name"};
            String[] values3 = {Operation.GET_ACCOUNT_HISTORY.toString()};
            int count = insert("operation", columns3, values3);
            if (count == -1) {
                connection.rollback(savepoint);
                return null;
            }
            int lastOpID = getLastOperationId();

            if (lastOpID != -1) {
                DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                Calendar cal = Calendar.getInstance();
                String time = dateFormat.format(cal.getTime()).replace("/", "-");
                String[] columns4 = {"user_id", "operation_id", "date"};
                String[] values4 = {my_id + "", lastOpID + "", time};
                count = insert("log", columns4, values4);
            } else {
                connection.rollback(savepoint);
                return null;
            }

            if (count == -1 || count == 0) {
                connection.rollback(savepoint);
                return null;
            }
            connection.commit();
            return resultList;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return null;
        } finally {
            Logger.write("GET ACCOUNT HISTORY FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean doPayForMobile(int my_id, String account_id, Mobile_Operator operator, String phoneNumber, double amount) {
        Logger.write("PAY FOR MOBILE STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);

        try {
            Savepoint savepoint = connection.setSavepoint();

            String[] columns = {"account_id"};
            String[] values = {account_id + ""};
            String owner_id = "";
            ResultSet resultSet = select("account", columns, values);
            while (resultSet.next()) {
                owner_id = resultSet.getString("user_id");
                if (owner_id == null) {
                    owner_id = resultSet.getString("org_id");
                }
            }

            if (!owner_id.equals(my_id + "") && role != Role.MODERATOR) {
                return  false;
            }

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "select * from " +
                    "account join organization on account.org_id = organization.org_id " +
                    "where name = \"" + operator.toString() + "\"");

            String account_to = "";
            ResultSet resultSet1 = preparedStatement.executeQuery();
            if (resultSet1.next()) {
                account_to = resultSet1.getString("account_id");
            }
            
            if (account_to != "") {
                return doTransaction(my_id, account_id, account_to, amount, "PAYED FOR MOBILE: " + operator + " \"" + phoneNumber + "\"");
            } else {
                connection.rollback(savepoint);
                return false;
            }
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("UT FOR MOBILE FINISHED", Logger.Mode.ACTION);
        }
    }

    public String generateAccountID() {
        String newID = "";
        ArrayList<String> account_ids = new ArrayList<String>();
        try {
            String[] columns = {};
            String[] values = {};
            ResultSet resultSet = select("account", columns, values);
            while (resultSet.next()) {
                account_ids.add(resultSet.getString("account_id"));
            }
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return null;
        } finally {
        }

        while (true) {
            String tempstr = "";
            for (int i = 0; i != account_id_lendth; i++) {
                Random random = new Random();
                tempstr += random.nextInt(1234) % 10;
            }
            if (!account_ids.contains(tempstr)) {
                newID = tempstr;
                break;
            }
        }
        return newID;
    }
    
    public boolean doSendMessage(int my_id, int receiver_id, String message) {
        Logger.write("SEND MESSAGE STARTED", Logger.Mode.ACTION);
        Role my_role = getRole(my_id);

        if (my_role.equals(Role.CLIENT) || my_role.equals(Role.ADMIN)) {
            if (receiver_id != -1) {
                Role receiver_role = getRole(receiver_id);
                if (receiver_role.equals(Role.CLIENT)) {
                    return false;
                }
            }
        }

        try {
            Savepoint savepoint = connection.setSavepoint();

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");
            int id = -1;
            if (my_role.equals(Role.CLIENT)) {
                String[] columns = {"sender_id", "message", "date"};
                String[] values = {my_id + "", message, time};

                id = insert("message", columns, values);
                if (id == -1) {
                    connection.rollback(savepoint);
                    return false;
                }
            } else {
                String[] columns = {"sender_id", "receiver_id", "message", "date"};
                String[] values = {my_id + "", receiver_id + "", message, time};

                id = insert("message", columns, values);
                if (id == -1) {
                    connection.rollback(savepoint);
                    return false;
                }
            }

            String[] columns3 = {"name", "message_id"};
            String[] values3 = {Operation.SEND_MESSAGE.toString(), id + ""};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return false;
            }


            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);


            if (id3 == -1) {
                connection.rollback(savepoint);
                return false;
            }
            connection.commit();
            return true;

        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("SEND MESSAGE FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean doReply(int my_id, int receiver_id, String message, int message_id) {
        Logger.write("REPLY STARTED", Logger.Mode.ACTION);
        Role my_role = getRole(my_id);

        try {
            Savepoint savepoint = connection.setSavepoint();

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");
            int id = -1;
            if (my_role.equals(Role.CLIENT)) {
                String[] columns = {"sender_id", "message", "date"};
                String[] values = {my_id + "", message, time};

                id = insert("message", columns, values);
                if (id == -1) {
                    connection.rollback(savepoint);
                    return false;
                }
            } else {
                String[] columns = {"sender_id", "receiver_id", "message", "date"};
                String[] values = {my_id + "", receiver_id + "", message, time};

                id = insert("message", columns, values);
                if (id == -1) {
                    connection.rollback(savepoint);
                    return false;
                }
            }

            String[] columns3 = {"name", "message_id"};
            String[] values3 = {Operation.REPLY.toString(), id + ""};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return false;
            }

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);


            if (id3 == -1) {
                connection.rollback(savepoint);
                return false;
            }

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "update message set answer_id = " + id + " where message_id = " + message_id
            );
            int count = preparedStatement.executeUpdate();
            if (count == 0) {
                return false;
            }
            connection.commit();
            return true;

        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("REPLY FINISHED", Logger.Mode.ACTION);
        }
    }


    public Message getMessageByID(int my_id, int message_id) {
        Logger.write("GET MESSAGE BY ID STARTED", Logger.Mode.ACTION);
        Role my_role = getRole(my_id);

        try {
            Savepoint savepoint = connection.setSavepoint();


            String[] columns = {"message_id"} ;
            String[] values = {message_id + ""};

            ResultSet resultSet = select("message", columns, values);
            int sender_id;
            int receiver_id;
            String message;
            String date;
            int answer_id;
            int isRead;
            if (!resultSet.next()) {
                connection.rollback(savepoint);
                return null;
            } else {
                sender_id = resultSet.getInt("sender_id");
                String res  = resultSet.getString("receiver_id");
                if (res == null) {
                    receiver_id = -1;
                } else {
                    receiver_id = Integer.parseInt(res);
                }
                message = resultSet.getString("message");
                date = resultSet.getString("date");
                res = resultSet.getString("answer_id");
                if (res == null) {
                    answer_id = -1;
                } else {
                    answer_id = Integer.parseInt(res);
                }
                isRead = resultSet.getInt("isread");
            }

            if (my_role.equals(Role.CLIENT)) {
                if (my_id != sender_id && my_id != receiver_id) {
                    return null;
                }
            } else if (my_role.equals(Role.MODERATOR)) {
                if (my_id != sender_id && receiver_id != -1 && receiver_id != my_id) {
                    return null;
                }
            } else if (my_role.equals(Role.ADMIN)) {
                if (my_id != sender_id && receiver_id != my_id) {
                    return null;
                }
            }

            String[] columns3 = {"name", "message_id"};
            String[] values3 = {Operation.GET_MESSAGE_BY_ID.toString(), message_id + ""};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return null;
            }

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return null;
            }
            connection.commit();
            if (isRead == 1) {
                return new Message(message_id, sender_id, receiver_id, message, date, answer_id, true);
            } else {
                return new Message(message_id, sender_id, receiver_id, message, date, answer_id, false);
            }
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return null;
        } finally {
            Logger.write("GET MESSAGE BY ID FINISHED", Logger.Mode.ACTION);
        }
    }

    public ArrayList<Message> doGetMessages(int my_id, int lower_range, int upped_range) {
        Logger.write("GET MESSAGES STARTED", Logger.Mode.ACTION);
        Role my_role = getRole(my_id);

        try {
            Savepoint savepoint = connection.setSavepoint();
            
            ArrayList<Message> messages = new ArrayList<Message>();

            if (my_role.equals(Role.CLIENT) || my_role.equals(Role.ADMIN)) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "select * from message where sender_id = " + my_id + " or receiver_id = " + my_id +
                        " order by message_id desc limit " + lower_range + ", " + (upped_range - lower_range)
                );
                ResultSet resultSet = preparedStatement.executeQuery();

                int message_id;
                int sender_id;
                int receiver_id;
                String message;
                String date;
                int answer_id;
                int isRead;
                while (resultSet.next()){
                    message_id = resultSet.getInt("message_id");
                    sender_id = resultSet.getInt("sender_id");
                    String res  = resultSet.getString("receiver_id");
                    if (res == null) {
                        receiver_id = -1;
                    } else {
                        receiver_id = Integer.parseInt(res);
                    }
                    message = resultSet.getString("message");
                    date = resultSet.getString("date");
                    res = resultSet.getString("answer_id");
                    if (res == null) {
                        answer_id = -1;
                    } else {
                        answer_id = Integer.parseInt(res);
                    }
                    isRead = resultSet.getInt("isread");
                    if (isRead == 1) {
                        messages.add(new Message(message_id, sender_id, receiver_id, message, date, answer_id, true));
                    } else {
                        messages.add(new Message(message_id, sender_id, receiver_id, message, date, answer_id, false));
                    }
                }
            } else {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "select * from message where sender_id = " + my_id + " or receiver_id = " + my_id +
                                " or receiver_id is null order by message_id desc limit " + lower_range + ", " + (upped_range - lower_range)
                );
                ResultSet resultSet = preparedStatement.executeQuery();

                int message_id;
                int sender_id;
                int receiver_id;
                String message;
                String date;
                int answer_id;
                int isRead;
                while (resultSet.next()){
                    message_id = resultSet.getInt("message_id");
                    sender_id = resultSet.getInt("sender_id");
                    String res  = resultSet.getString("receiver_id");
                    if (res == null) {
                        receiver_id = -1;
                    } else {
                        receiver_id = Integer.parseInt(res);
                    }
                    message = resultSet.getString("message");
                    date = resultSet.getString("date");
                    res = resultSet.getString("answer_id");
                    if (res == null) {
                        answer_id = -1;
                    } else {
                        answer_id = Integer.parseInt(res);
                    }
                    isRead = resultSet.getInt("isread");
                    if (isRead == 1) {
                        messages.add(new Message(message_id, sender_id, receiver_id, message, date, answer_id, true));
                    } else {
                        messages.add(new Message(message_id, sender_id, receiver_id, message, date, answer_id, false));
                    }
                }
            }

            String[] columns3 = {"name"};
            String[] values3 = {Operation.GET_MESSAGES.toString()};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return null;
            }

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return null;
            }
            connection.commit();
            return messages;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return null;
        } finally {
            Logger.write("GET MESSAGES FINISHED", Logger.Mode.ACTION);
        }
    }
    
    public int doGetNumberOfUnreadMessages(int my_id) {
        Logger.write("GET NUMBER OF UNREAD STARTED", Logger.Mode.ACTION);
        Role my_role = getRole(my_id);

        try {
            Savepoint savepoint = connection.setSavepoint();

            int num = 0;
            if (my_role.equals(Role.CLIENT) || my_role.equals(Role.ADMIN)) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "select count(*) as num from message where (receiver_id = " + my_id +
                        ") and isread = 0"
                );
                ResultSet resultSet = preparedStatement.executeQuery();

                if (resultSet.next()){
                    num = resultSet.getInt("num");
                }
            } else {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "select count(*) as num from message where (receiver_id = " + my_id +
                        " or receiver_id is null) and isread = 0"
                );
                ResultSet resultSet = preparedStatement.executeQuery();

                if (resultSet.next()){
                    num = resultSet.getInt("num");
                }
            }

            String[] columns3 = {"name"};
            String[] values3 = {Operation.GET_NUMBER_OF_UNREAD_MESSAGES.toString()};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return -1;
            }

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return -1;
            }
            connection.commit();
            return num;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return -1;
        } finally {
            Logger.write("GET NUMBER OF UNREAD FINISHED", Logger.Mode.ACTION);
        }
    }

    public int doGetNumberOfMessages(int my_id) {
        Logger.write("GET NUMBER OF MESSAGES STARTED", Logger.Mode.ACTION);
        Role my_role = getRole(my_id);

        try {
            Savepoint savepoint = connection.setSavepoint();

            int num = 0;
            if (my_role.equals(Role.CLIENT) || my_role.equals(Role.ADMIN)) {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "select count(*) as num from message where (sender_id = " + my_id + " or receiver_id = " + my_id +
                                ")"
                );
                ResultSet resultSet = preparedStatement.executeQuery();

                if (resultSet.next()){
                    num = resultSet.getInt("num");
                }
            } else {
                PreparedStatement preparedStatement = connection.prepareStatement(
                        "select count(*) as num from message where (sender_id = " + my_id + " or receiver_id = " + my_id +
                                " or receiver_id is null)"
                );
                ResultSet resultSet = preparedStatement.executeQuery();

                if (resultSet.next()){
                    num = resultSet.getInt("num");
                }
            }

            String[] columns3 = {"name"};
            String[] values3 = {Operation.GET_NUMBER_OF_MESSAGES.toString()};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return -1;
            }

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return -1;
            }
            connection.commit();
            return num;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return -1;
        } finally {
            Logger.write("GET NUMBER OF UNREAD FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean addCookie(int id, String secret) {
        Logger.write("ADD COOKIE STARTED", Logger.Mode.ACTION);

        try {
            Savepoint savepoint = connection.setSavepoint();

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns = {"id"};
            String[] values = {id + ""};

            ResultSet resultSet = select("cookie", columns, values);
            if (resultSet.next()) {
                PreparedStatement statement = connection.prepareStatement("update cookie set value = \"" + secret +"\" , isDisabled = 0 where id = " + id);
                statement.executeUpdate();
            } else {
                String[] columns2 = {"id", "value", "isDisabled"};
                String[] values2 = {id + "", secret, "0"};

                insert("cookie", columns2, values2);
            }
            connection.commit();
            return true;

        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("ADD COOKIE FINISHED", Logger.Mode.ACTION);
        }
    }

    public String getRandomString() {
        Random random = new Random();
        String res = "";
        for (int i = 0; i != 40; i++) {
            res += Math.abs(random.nextInt() % 10);
        }
        return res;
    }
    
    public boolean disableCookie(int id, String secret) {
        Logger.write("DISABLE COOKIE STARTED", Logger.Mode.ACTION);

        try {
            Savepoint savepoint = connection.setSavepoint();

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns = {"id", "value"};
            String[] values = {id + "", secret + ""};

            ResultSet resultSet = select("cookie", columns, values);
            if (resultSet.next()) {
                PreparedStatement statement = connection.prepareStatement("update cookie set isDisabled = 1 where id = " + id);
                statement.executeUpdate();
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("DISABLE COOKIE FINISHED", Logger.Mode.ACTION);
        }
    }
    
    public boolean isCookieOk(int id, String secret) {
        Logger.write("IS COOKIE OK STARTED", Logger.Mode.ACTION);

        try {
            Savepoint savepoint = connection.setSavepoint();

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns = {"id"};
            String[] values = {id + ""};

            ResultSet resultSet = select("cookie", columns, values);
            if (resultSet.next()) {
                String oldSecret = resultSet.getString("value");
                int isDisabled = 0;
                isDisabled = resultSet.getInt("isDisabled");
                if (oldSecret.equals(secret) && isDisabled == 0) {
                    return true;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("IS COOKIE OK FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean doBlockUser(int my_id, int user_id, String note) {
        Logger.write("BLOCK USER STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);
        if (role != Role.ADMIN) {
            return false;
        }
        try {
            Savepoint savepoint = connection.setSavepoint();

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            PreparedStatement statement = connection.prepareStatement("update login set isblocked = 1 where user_id = " + user_id);
            int count = statement.executeUpdate();
            if (count == 0) {
                connection.rollback(savepoint);
                return false;
            }
            String[] columns3 = {"name", "note"};
            String[] values3 = {Operation.USER_BLOCKED.toString(), note};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return false;
            }

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return false;
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("BLOCK USER FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean doUnBlockUser(int my_id, int user_id, String note) {
        Logger.write("UNBLOCK USER STARTED", Logger.Mode.ACTION);
        Role role = getRole(my_id);
        if (role != Role.ADMIN) {
            return false;
        }
        try {
            Savepoint savepoint = connection.setSavepoint();

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            PreparedStatement statement = connection.prepareStatement("update login set isblocked = 0 where user_id = " + user_id);
            int count = statement.executeUpdate();
            if (count == 0) {
                connection.rollback(savepoint);
                return false;
            }
            String[] columns3 = {"name", "note"};
            String[] values3 = {Operation.USER_BLOCKED.toString(), note};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return false;
            }

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return false;
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("UNBLOCK USER FINISHED", Logger.Mode.ACTION);
        }
    }

    public String getNameByID(int my_id, int user_id) {
        Logger.write("GET NAME BY ID STARTED", Logger.Mode.ACTION);

        try {
            Savepoint savepoint = connection.setSavepoint();

            String[] columns = {"user_id"};
            String[] values = {user_id + ""};
            ResultSet resultSet = select("user", columns, values);
            String name = "";
            if (resultSet.next()) {
                name += resultSet.getString("lastname");
                name += " " + resultSet.getString("firstname");
            }

            String[] columns3 = {"name"};
            String[] values3 = {Operation.GET_USER_NAME.toString()};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return "";
            }
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return "";
            }
            connection.commit();
            return name;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return "";
        } finally {
            Logger.write("GET USER NAME FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean doMarkMessageRead(int my_id, int message_id) {
        Logger.write("MARK MESSAGE READ STARTED", Logger.Mode.ACTION);
        try {
            Savepoint savepoint = connection.setSavepoint();

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "update message set isread = 1 where message_id = " + message_id
            );
            int count = preparedStatement.executeUpdate();
            if (count == 0) {
                connection.rollback(savepoint);
                return false;
            }
            String[] columns3 = {"name"};
            String[] values3 = {Operation.MARK_MESSAGE_READ.toString()};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return false;
            }
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return false;
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("MARK MESSAGE READ FINISHED", Logger.Mode.ACTION);
        }
    }

    public boolean doMarkMessageUnread(int my_id, int message_id) {
        Logger.write("MARK MESSAGE UNREAD STARTED", Logger.Mode.ACTION);
        try {
            Savepoint savepoint = connection.setSavepoint();

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "update message set isread = 0 where message_id = " + message_id
            );
            int count = preparedStatement.executeUpdate();
            if (count == 0) {
                connection.rollback(savepoint);
                return false;
            }
            String[] columns3 = {"name"};
            String[] values3 = {Operation.MARK_MESSAGE_UNREAD.toString()};
            int id2 = insert("operation", columns3, values3);
            if (id2 == -1) {
                connection.rollback(savepoint);
                return false;
            }
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Calendar cal = Calendar.getInstance();
            String time = dateFormat.format(cal.getTime()).replace("/", "-");

            String[] columns4 = {"user_id", "operation_id", "date"};
            String[] values4 = {my_id + "", id2 + "", time};
            int id3 = insert("log", columns4, values4);

            if (id3 == -1) {
                connection.rollback(savepoint);
                return false;
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            Logger.write(e.getMessage(), Logger.Mode.ERROR);
            return false;
        } finally {
            Logger.write("MARK MESSAGE UNREAD FINISHED", Logger.Mode.ACTION);
        }
    }
}